# -*- coding: utf-8 -*-

'''
将数据从数据库中导出到excel中
'''

import os
import xlrd
import pymssql
import shutil

MSSQL_KEY = {'host':'localhost', 'port':1433, 'user':'foo', 'password':'bar', 'database':'FOOBAR'}
LANG_INSERT = 'insert into t_p_rooms_appoint_qudao (saletime,cityarea,zutuan,developer,basename,usetype,usetype_detail,bArea,iArea,jmjg,ct,xls) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'

FIRST_ROW = ['日期', '区县', '组团', '企业', '项目', '业态', '细分业态', '建面', '套内', '金额', '套数']

TOTAL_COUNT = 0

class InvalidExellError(Exception):
    'Invalid Exell Error'

def insertdata(infos):
    with pymssql.connect(**MSSQL_KEY) as conn:
        with conn.cursor() as csr:
            csr.execute(LANG_INSERT, infos)
            conn.commit()

def migrant(fi):
    global TOTAL_COUNT
    with pymssql.connect(**MSSQL_KEY) as conn:
        with conn.cursor() as csr:
            with xlrd.open_workbook(fi) as workbook:
                sheets = workbook.sheets()
                for sheet in sheets:
                    cols = sheet.ncols
                    rows = sheet.nrows
                    
                    # print("rows:%s, columns:%s" % (rows-1,cols))

                    info0 = [sheet.cell_value(0, i).strip() for i in range(cols)][0:11]
                    print(info0)
                    if info0 != FIRST_ROW:
                        raise InvalidExellError
                    
                    TOTAL_COUNT += (rows-1)
                    # infos = []
                    n = 0
                    for r in range(1, rows):
                        infor = [sheet.cell_value(r, c) for c in range(11)]
                        infor.append(fi)
                        infor[0] = xlrd.xldate_as_datetime(infor[0], 0)
                        csr.execute(LANG_INSERT, tuple(infor))
                        n +=1

                    conn.commit()
                        
                    print('totalrows:%s, insert:%s, last: %s' % (rows-1, n, rows-1-n))

def main():
    invalidfiles = []
    # datafile = 'data'
    # datafile = 'data\\cq2018'
    datafile = 'data\\cq2019'
    xls = [os.path.join(datafile, fn) for fn in os.listdir(datafile) if (fn[0] != '~' and os.path.isfile(os.path.join(datafile, fn)))]

    for fi in xls:
        print(fi)
        try:
            migrant(fi)
            shutil.move(fi, '{0}\\success{1}'.format(datafile, fi[len(datafile):]))
        except InvalidExellError as ex:
            invalidfiles.append(fi)
            print(ex)
    print(TOTAL_COUNT)
            

if __name__ == '__main__':
    main()
